using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.ObjectModel;
using System.Collections;
using System.Text.RegularExpressions;
using Destra.Tools.DACGenerator.SqlSuggestor;

namespace Destra.Tools.DACGenerator.SqlHierarchySuggestor
{
    public partial class SqlHierarchySuggestor : UserControl
    {
        private const string c_serversText = "<...>";

        public SqlHierarchySuggestor()
        {
            InitializeComponent();

            ServerTextBox.ReadOnly = true;
        }

        private void SqlHierarchySuggestor_Load(object sender, EventArgs e)
        {
            LoadDatabases();
        }

        private void LoadDatabases()
        {
            DatabaseDropdown.Items.Clear();
            ObjectDropdown.Items.Clear();
            ParentColumnDropdown.Items.Clear();
            ChildColumnDropdown.Items.Clear(); 
            SqlQueryTextbox.Clear();

            if (!string.IsNullOrEmpty(ServerTextBox.Text))
            {
                foreach (Database database in SqlCmoHelper.SqlDatabases(ServerTextBox.Text))
                {
                    DatabaseDropdown.Items.Add(database.Name);
                }
            }
        }

        private void DatabaseDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            ObjectDropdown.Items.Clear();
            ParentColumnDropdown.Items.Clear();
            ChildColumnDropdown.Items.Clear(); 
            SqlQueryTextbox.Clear();

            if (!string.IsNullOrEmpty(ServerTextBox.Text) && !string.IsNullOrEmpty(DatabaseDropdown.Text))
            {
                foreach (Table table in SqlCmoHelper.GetTables(ServerTextBox.Text, DatabaseDropdown.Text))
                {
                    if (!table.Name.StartsWith("sys") && !table.Name.StartsWith("dtproperties"))
                    {
                        ObjectDropdown.Items.Add(table.Schema + "." + table.Name);
                    }
                }
            }
        }

        private void ObjectDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(DatabaseDropdown.Text) && !string.IsNullOrEmpty(ObjectDropdown.Text))
            {
                ParentColumnDropdown.Items.Clear();
                ChildColumnDropdown.Items.Clear();
                SqlQueryTextbox.Clear();

                foreach (Column col in SqlCmoHelper.GetTable(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]).Columns)
                {
                    ParentColumnDropdown.Items.Add(col.Name);
                }

                foreach (Column col in SqlCmoHelper.GetTable(ServerTextBox.Text, DatabaseDropdown.Text, ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]).Columns)
                {
                    ChildColumnDropdown.Items.Add(col.Name);
                }
            }
        }

        private void ParentColumnDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateSql();
        }

        private void ChildColumnDropdown_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateSql();
        }

        private void GenerateSql()
        {
            if (!string.IsNullOrEmpty(ParentColumnDropdown.Text) && !string.IsNullOrEmpty(ChildColumnDropdown.Text))
            {
                StringBuilder sqlBuilder = new StringBuilder();
                StringBuilder columnsBuilder = new StringBuilder();
                StringBuilder columnsCTEBuilder = new StringBuilder();

                foreach (Column col in SqlCmoHelper.GetTable(ServerTextBox.Text, DatabaseDropdown.Text,ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]).Columns)
                {
                    columnsBuilder.AppendFormat("{0}, ", col.Name);
                    columnsCTEBuilder.AppendFormat("REC.{0}, ", col.Name);
                }
                string columnNames = columnsBuilder.ToString(0, columnsBuilder.Length - 2).Trim();
                string columnNamesCTE = columnsCTEBuilder.ToString(0, columnsCTEBuilder.Length - 2).Trim();

                string objectNameWithoutSchema = ObjectDropdown.Text;
                if (objectNameWithoutSchema.Contains("."))
                {
                    objectNameWithoutSchema = objectNameWithoutSchema.Substring(objectNameWithoutSchema.IndexOf(".") + 1);
                }

                sqlBuilder.AppendLine(string.Format("WITH {0}Hierarchy ({1}, HierarchyLevel) AS", objectNameWithoutSchema, columnNames));
                sqlBuilder.AppendLine("(");
                sqlBuilder.AppendLine("\t-- Base case");
                sqlBuilder.AppendLine(string.Format("\tSELECT {0}, 1 as HierarchyLevel", columnNames));
                sqlBuilder.AppendLine(string.Format("\tFROM [{0}].[{1}]", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]));
                sqlBuilder.AppendLine(string.Format("\tWHERE {0} IS NULL", ParentColumnDropdown.Text));
                sqlBuilder.AppendLine();
                sqlBuilder.AppendLine("\tUNION ALL");
                sqlBuilder.AppendLine("\t-- Recursive step");
                sqlBuilder.AppendLine(string.Format("\tSELECT {0}, CTE.HierarchyLevel + 1 AS HierarchyLevel", columnNamesCTE));
                sqlBuilder.AppendLine(string.Format("\tFROM [{0}].[{1}] AS REC ", ObjectDropdown.Text.Split(".".ToCharArray())[0], ObjectDropdown.Text.Split(".".ToCharArray())[1]));
                sqlBuilder.AppendLine(string.Format("\t\tINNER JOIN {0}Hierarchy AS CTE ON REC.{1} = CTE.{2}", objectNameWithoutSchema, ParentColumnDropdown.Text, ChildColumnDropdown.Text));
                sqlBuilder.AppendLine(")");
                sqlBuilder.AppendLine(string.Format("SELECT {0}, HierarchyLevel", columnNames));
                sqlBuilder.AppendLine(string.Format("FROM {0}Hierarchy", objectNameWithoutSchema));
                sqlBuilder.AppendLine("ORDER BY HierarchyLevel");
                SqlQueryTextbox.Text = sqlBuilder.ToString();

                try
                {
                    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Trusted_Connection=yes", ServerTextBox.Text, DatabaseDropdown.Text)))
                    {
                        conn.Open();
                        using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sqlBuilder.ToString(), conn))
                        {
                            DataTable dt = new DataTable();
                            adapter.Fill(dt);

                            ResultsGridView.DataSource = dt;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        public string SQL
        {
            get
            {
                return SqlQueryTextbox.Text;
            }
        }

        public string Server
        {
            set
            {
                ServerTextBox.Text = value;

                LoadDatabases();
            }
        }

        public string Database
        {
            get
            {
                return (string)DatabaseDropdown.SelectedItem;
            }
            set
            {
                DatabaseDropdown.SelectedItem = value;
            }
        }

        public string Object
        {
            get
            {
                return (string)ObjectDropdown.SelectedItem;
            }
            set
            {
                ObjectDropdown.SelectedItem = value;
            }
        }
    }
}
